<?php
/**
 * 数据库迁移脚本
 * 用于更新考勤记录表结构，支持上午下午分开记录
 */

session_start();
require_once 'includes/config.php';
require_once 'includes/database.php';

// 检查是否已登录且有管理员权限
if (!isset($_SESSION['user_id']) || $_SESSION['user_role'] !== 'super_admin') {
    die('需要管理员权限才能执行数据库迁移');
}

try {
    $database = getDatabaseConnection();
    
    echo "<h2>数据库迁移开始...</h2>";
    
    // 检查表是否存在
    $checkTableSql = "SHOW TABLES LIKE 'attendance_records'";
    $stmt = $database->query($checkTableSql);
    $tableExists = $stmt->rowCount() > 0;
    
    if (!$tableExists) {
        die("考勤记录表不存在，请先运行安装程序");
    }
    
    // 检查新字段是否已存在
    $checkColumnsSql = "SHOW COLUMNS FROM attendance_records LIKE 'morning_clock_in'";
    $stmt = $database->query($checkColumnsSql);
    $columnsExist = $stmt->rowCount() > 0;
    
    if ($columnsExist) {
        echo "<p style='color: green;'>数据库结构已是最新版本，无需迁移</p>";
        exit;
    }
    
    echo "<p>开始更新考勤记录表结构...</p>";
    
    // 备份现有数据
    echo "<p>备份现有数据...</p>";
    $backupSql = "CREATE TABLE IF NOT EXISTS attendance_records_backup AS SELECT * FROM attendance_records";
    $database->exec($backupSql);
    
    // 添加新字段
    $alterSql = "
        ALTER TABLE attendance_records 
        ADD COLUMN morning_clock_in TIME NULL AFTER record_date,
        ADD COLUMN morning_clock_out TIME NULL AFTER morning_clock_in,
        ADD COLUMN morning_work_hours DECIMAL(5,2) DEFAULT 0 AFTER morning_clock_out,
        ADD COLUMN afternoon_clock_in TIME NULL AFTER morning_work_hours,
        ADD COLUMN afternoon_clock_out TIME NULL AFTER afternoon_clock_in,
        ADD COLUMN afternoon_work_hours DECIMAL(5,2) DEFAULT 0 AFTER afternoon_clock_out,
        ADD COLUMN all_day_work_hours DECIMAL(5,2) DEFAULT 0 AFTER afternoon_work_hours,
        ADD COLUMN total_work_hours DECIMAL(5,2) DEFAULT 0 AFTER all_day_work_hours,
        MODIFY COLUMN status ENUM('normal', 'late', 'early_leave', 'absent', 'leave', 'business_trip', 'overtime') DEFAULT 'normal'
    ";
    
    $database->exec($alterSql);
    echo "<p style='color: green;'>成功添加新字段</p>";
    
    // 迁移现有数据
    echo "<p>迁移现有数据...</p>";
    
    // 将现有的 clock_in, clock_out, work_hours 迁移到上午记录
    $migrateSql = "
        UPDATE attendance_records 
        SET 
            morning_clock_in = clock_in,
            morning_clock_out = clock_out,
            morning_work_hours = work_hours,
            total_work_hours = work_hours
        WHERE clock_in IS NOT NULL AND clock_out IS NOT NULL
    ";
    
    $database->exec($migrateSql);
    echo "<p style='color: green;'>成功迁移现有数据</p>";
    
    // 删除旧字段（可选，暂时保留以兼容旧代码）
    // $dropSql = "ALTER TABLE attendance_records DROP COLUMN clock_in, DROP COLUMN clock_out, DROP COLUMN work_hours";
    // $database->exec($dropSql);
    
    echo "<h3 style='color: green;'>数据库迁移完成！</h3>";
    echo "<p>现有数据已成功迁移到新的表结构中。</p>";
    echo "<p>备份表: attendance_records_backup</p>";
    
} catch (PDOException $e) {
    echo "<p style='color: red;'>迁移失败: " . $e->getMessage() . "</p>";
    echo "<p>请检查数据库连接和权限。</p>";
}
?>
